#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "

insert into view_consult_dws.dws_view
-- year
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'all' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'all' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'all' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'all' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,dayinfo;



insert into view_consult_dws.dws_view
-- year+area
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, area, country,province,city
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo, area, country, province,city
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo, area, country, province,city
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,dayinfo,area,country,province,city;



insert into view_consult_dws.dws_view
-- year+origin_channel
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
origin_channel,
null as from_url,
'origin_channel' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, origin_channel
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
origin_channel,
null as from_url,
'origin_channel' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo, origin_channel
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
origin_channel,
null as from_url,
'origin_channel' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,origin_channel
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
origin_channel,
null as from_url,
'origin_channel' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,dayinfo,origin_channel;


insert into view_consult_dws.dws_view
-- year+seo_source
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'seo_source' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, seo_source
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'seo_source' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo, seo_source
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'seo_source' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,seo_source
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
null as from_url,
'seo_source' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,dayinfo,seo_source;

insert into view_consult_dws.dws_view
-- year+from_url
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
from_url,
'from_url' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, from_url
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
from_url,
'from_url' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo, from_url
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
from_url,
'from_url' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,from_url
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
null as seo_source,
null as area,
null as country,
null as province,
null as city,
null as origin_channel,
from_url,
'from_url' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult group by yearinfo, monthinfo,quarterinfo,dayinfo,from_url;




insert into view_consult_dws.dws_consult
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
null as monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'year' as time_type
from view_consult_dwb.dwb_view_consult where msg_count > 0 group by yearinfo, area, country,province,city
union all
-- month
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
null as quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'month' as time_type
from view_consult_dwb.dwb_view_consult where msg_count > 0 group by yearinfo, monthinfo, area, country, province,city
union all
--季度
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
null as dayinfo,
quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'quarter' as time_type
from view_consult_dwb.dwb_view_consult where msg_count > 0 group by yearinfo, monthinfo,quarterinfo, area, country, province,city
union all
--天
select
count(distinct session_id) as session_cnt,
count(distinct sid) as id_cnt,
count(distinct ip) as ip_cnt,
yearinfo,
monthinfo,
dayinfo,
quarterinfo,
null as seo_source,
area,
country,
province,
city,
null as origin_channel,
null as from_url,
'area' as group_type,
'day' as time_type
from view_consult_dwb.dwb_view_consult where msg_count > 0 group by yearinfo, monthinfo,quarterinfo,dayinfo,area,country,province,city;




"
